package com.neuedu.dbutils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;

/**
 * @auth 金山老师
 * title:   书籍代码
 * creatime: 2022-10-12 14:27
 * 描述 :
 */
public class JDBCUtils {


    private static String url = "";
    private static String username = "";
    private static String password = "";
    private static String driverClass = "";

    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");

        url = bundle.getString("url");
        username = bundle.getString("username");
        password = bundle.getString("password");
        driverClass = bundle.getString("driverClass");
    }


    /**
     * 加载驱动，创建连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName(driverClass);
        Connection con = DriverManager.getConnection(url, username, password);


        return con;
    }


    /**
     * 用于喜欢性 update delete  insert 类型的sql
     * @param con
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int update(Connection con , String sql, Object ... params) throws SQLException {
        PreparedStatement stmt = null;
        int count = 0;
        try {

            stmt = con.prepareStatement(sql);

            System.out.println("sql = " + sql);

            //绑定变量
            if(params!= null && params.length>0) {
                int index = 0;
                for (Object param : params) {
                    stmt.setObject(++index,param);
                }
            }
            count = stmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(stmt != null){
                stmt.close();
            }

        }

        return count;
    }


    /**
     * 查询单条记录
     * @param conn
     * @param claszz
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T selectOne(Connection conn ,Class claszz ,String sql , Object ... params){

        List<T> list = selectList(conn, claszz, sql, params);

        return list.size()>0? list.get(0):null;
    }










    /**
     * 根据sql 查询总条数
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static long selectTotal(Connection conn,String sql ,Object ... params){
        sql = "SELECT COUNT(1) COUNT FROM ("+ sql+" ) t ";
        long count = (long) JDBCUtils.selectObject(conn, sql ,params );
        return count;
    }


    /**
     * 查询一行一列的对象
     * @param conn
     * @param sql
     * @param params
     *
     * SELECT username FROM USER t WHERE t.`id` = 1;
     * SELECT COUNT(1) COUNT FROM USER t
     * SELECT COUNT(1) COUNT FROM USER t WHERE 1=0
     * @return
     */
    public static Object selectObject(Connection conn ,String sql , Object ... params){
        //        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        Object object = null;
        try {

            stmt = conn.prepareStatement(sql);

            //绑定变量
            if(params!= null && params.length>0) {
                int index = 0;
                for (Object param : params) {
                    stmt.setObject(++index,param);
                }
            }

            //执行sql
            rs = stmt.executeQuery();

            if(rs.next()){
                object =  rs.getObject(1);
            }

        }catch (SQLException throwables) {
            throwables.printStackTrace();

        }  finally {
            //            close(conn,stmt, rs);
        }

        return object;
    }


    /**
     * 根据条件查询分页数据
     * @param conn
     * @param clazz
     * @param sql
     * @param current
     * @param size
     * @param params
     * @param <T>
     * @return
     */
    public static <T> List<T> selectPage( Connection conn,Class clazz ,String sql ,int current,int size, Object ... params){

        int start  = (current - 1) * size;
        sql = " select * from ( "+sql+" ) t limit "+start+" ,"+size;  //分页的sql
        System.out.println("sql = " + sql);
        return  selectList(conn , clazz ,sql ,params);
    }


    /**
     * 根据条件查询集合
     * @param conn
     * @param claszz
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    public static <T> List<T> selectList(Connection conn ,Class claszz ,String sql , Object ... params){
//        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List list = new ArrayList();
        try {
           
            stmt = conn.prepareStatement(sql);

            //绑定变量
            if(params!= null && params.length>0) {
                int index = 0;
                for (Object param : params) {
                    stmt.setObject(++index,param);
                }
            }


            //执行sql
            rs = stmt.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount(); //查询结果中字段的数量
            List<String> columnNames = new ArrayList();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(meta.getColumnLabel(i));
            }


            while(rs.next()){ //一行，
                Object lineObject = claszz.newInstance();
                for (String columnName : columnNames) {
                    Object columValue = rs.getObject(columnName);//Integer String Date   LocalDate  LocalTime   LocalDateTime
                    try {
                        Field field = getField(claszz,columnName);
                        //Role-  role roleName  ---> 测试反射角色
                        field.setAccessible(true);  //can not access a member of class com.neuedu.dbutil.entity.Role with modifiers "private"
                        field.set(lineObject, columValue);
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }

                list.add(lineObject);
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }  finally {
//            close(conn,stmt, rs);
        }

        return list;
    }


    public static  Map selectMap(Connection conn   , String sql , Object ... params) {

        List<Map> list = selectMapList(conn,sql,params);
        return list.size() > 0 ? list.get(0) : null;
    }


    /**
     * 查询集合《Map》
     * @param conn
     * @param sql
     * @param params
     * @return
     */

    public static  List<Map> selectMapList(Connection conn   , String sql , Object ... params) {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List<Map> list = new ArrayList();
        try {

            stmt = conn.prepareStatement(sql);

            //绑定变量
            if(params!= null && params.length>0) {
                int index = 0;
                for (Object param : params) {
                    stmt.setObject(++index,param);
                }
            }

            //执行sql
            rs = stmt.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount(); //查询结果中字段的数量
            List<String> columnNames = new ArrayList();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(meta.getColumnLabel(i));
            }


            while(rs.next()){
               Map map = new HashMap();
                for (String columnName : columnNames) {
                    Object columnValue = rs.getObject(columnName);
                    map.put(columnName,columnValue);
                }

                list.add(map);
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }  finally {
            //            close(conn,stmt, rs);
        }

        return list;
    }
//    根据字段名字获取Fidld

    private  static Field getField(Class claszz,String columnName){
        Field field = null;
        try {
            field = claszz.getDeclaredField(columnName);  //  Integer active    int active
        } catch (NoSuchFieldException e) {
            //e.printStackTrace();
        }
        //属性 跟 字段同名
        if(field != null){
            return field;
        }

        //columnName  role_id  role_name
        // @TableColumn(name ='role_id'  )Integer roleId
        // @TableColumn(name = 'role_name')String roleName
        //遍历所有属性，是否带有 TableColumn注解， name = columnName
        Field[] declaredFields = claszz.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            TableColumn annotation = declaredField.getAnnotation(TableColumn.class);
            if(annotation !=null  && columnName.equals(annotation.name()) ){
//                field = declaredField;
               return declaredField;
            }
        }

        return null;

    }




}
